﻿using LandManager.Web.Models;
using LandManager.Web.Service;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace LandManager.Web.Controllers
{
    public class LandController : Controller
    {
        LandService landService = new LandService();

        //
        // GET: /Land/

        //public ActionResult Index()
        //{
        //    var landList = landService.GetList();

        //    ViewBag.Menu = "数据修改";
        //    return View(landList);
        //}

        public ActionResult Index(int handle = 0)
        {
            var landList = landService.GetList(handle);

            ViewBag.Menu = "数据修改";
            ViewBag.Table = handle == 0 ? "handleland" : "addland";
            return View(landList);
        }

        public ActionResult Update()
        {
            ViewBag.Menu = "数据修改";
            return View();
        }

        public ActionResult Import()
        {
            ViewBag.Menu = "数据录入";
            return View();
        }

        [HttpPost]
        public ActionResult Import(HttpPostedFileBase landfile)
        {
            AgencyService agencyService = new AgencyService();
            //Request.Files["landfile"]
            DataSet ds = new DataSet();
            var resultMessage = "";

            try
            {
                if (landfile != null && landfile.ContentLength > 0)
                {
                    string fileExtension = Path.GetExtension(landfile.FileName);

                    if (fileExtension == ".xls" || fileExtension == ".xlsx")
                    {
                        string strFilePath = Server.MapPath("~/Content/") + Path.GetFileName(landfile.FileName);
                        if (System.IO.File.Exists(strFilePath))
                        {
                            System.IO.File.Delete(strFilePath);
                        }

                        landfile.SaveAs(strFilePath);
                        using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
                        {
                            HSSFWorkbook workbook = new HSSFWorkbook(file);
                            ISheet sheet = workbook.GetSheetAt(0);

                            //写入内容
                            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                            int successCount = 0;
                            int failedCount = 0;

                            DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
                            dtFormat.ShortDatePattern = "yyyy/MM/dd";

                            while (rows.MoveNext())
                            {
                                NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;

                                if (row.GetCell(0) == null || row.GetCell(0).ToString() == "宗地编号"
                                    || string.IsNullOrEmpty(row.GetCell(0).ToString())
                                    || row.GetCell(0).ToString().StartsWith("说明"))
                                    continue;

                                LandInfo m = new LandInfo();

                                #region 获取excel种的值并赋值给LandInfo
                                //宗地编号
                                m.ZDBH = Decimal.Parse(row.GetCell(0) == null ? "" : row.GetCell(0).ToString());
                                //地籍号
                                m.DJH = int.Parse(row.GetCell(1) == null ? "" : row.GetCell(1).ToString());
                                //土地面积
                                m.land_area = int.Parse(row.GetCell(2) == null ? "" : row.GetCell(2).ToString());
                                //用地性质
                                m.land_type = row.GetCell(3) == null ? "" : row.GetCell(3).ToString();

                                //所属二级单位
                                var agencyName = row.GetCell(4) == null ? "" : row.GetCell(4).ToString();
                                var a = agencyService.FindByName(agencyName);
                                if (a == null)
                                    m.land_agency = 1;
                                else
                                    m.land_agency = a.agency_ID;

                                //是否为上市公司资产
                                var isIPO = row.GetCell(5) == null ? "" : row.GetCell(5).ToString();
                                if (isIPO == "是")
                                    m.land_Is_IPO = 1;
                                else
                                    m.land_Is_IPO = 0;

                                //取得时间
                                var date = row.GetCell(6) == null ? "" : row.GetCell(6).ToString();
                                if (string.IsNullOrEmpty(date))
                                    m.land_ownDate = DateTime.Now;
                                else
                                    m.land_ownDate = Convert.ToDateTime(date, dtFormat);
                                //账面原值
                                m.land_price = Decimal.Parse(row.GetCell(7) == null ? "" : row.GetCell(7).ToString());
                                //土地使用范围
                                m.land_range = row.GetCell(8) == null ? "" : row.GetCell(8).ToString();
                                //土地取得方式
                                m.land_getway = row.GetCell(9) == null ? "" : row.GetCell(9).ToString();
                                //宗地位置
                                m.land_location = row.GetCell(10) == null ? "" : row.GetCell(10).ToString();
                                //土地使用人
                                m.land_user = row.GetCell(11) == null ? "" : row.GetCell(11).ToString();
                                //实际使用人
                                m.land_actual_user = row.GetCell(12) == null ? "" : row.GetCell(12).ToString();

                                //使用者与权力人是否一致
                                var userEqul = row.GetCell(13) == null ? "" : row.GetCell(13).ToString();
                                if (userEqul == "是")
                                    m.land_is_usereqlobligee = 1;
                                else
                                    m.land_is_usereqlobligee = 0;

                                //土地证办理情况
                                m.land_certificate_status = row.GetCell(14) == null ? "" : row.GetCell(14).ToString();
                                //土地使用证权号
                                m.land_certificate_ID = row.GetCell(15) == null ? "" : row.GetCell(15).ToString();

                                //发证时间
                                var certificate_date = row.GetCell(16) == null ? "" : row.GetCell(16).ToString();
                                if (!string.IsNullOrEmpty(certificate_date))
                                    m.land_certificate_Date = Convert.ToDateTime(certificate_date, dtFormat);
                                //发证机关
                                m.land_certificate_agency = row.GetCell(17) == null ? "" : row.GetCell(17).ToString();
                                //未办理原因
                                m.land_certificate_reason = row.GetCell(18) == null ? "" : row.GetCell(18).ToString();
                                //其他原因
                                m.land_certificate_others = row.GetCell(19) == null ? "" : row.GetCell(19).ToString();
                                //现地上物情况
                                m.land_up_desc = row.GetCell(20) == null ? "" : row.GetCell(20).ToString();
                                //是否出租
                                m.land_Is_rent = row.GetCell(21) == null ? "" : row.GetCell(21).ToString();
                                //出租对象
                                m.land_rent_obj = row.GetCell(22) == null ? "" : row.GetCell(22).ToString();

                                //租期时间
                                var land_rent_date = row.GetCell(23) == null ? "" : row.GetCell(23).ToString();
                                if (!string.IsNullOrEmpty(land_rent_date))
                                    m.land_rent_date = Convert.ToDateTime(land_rent_date, dtFormat);

                                //是否有出租合同
                                var land_rent_contract = row.GetCell(24) == null ? "" : row.GetCell(24).ToString();
                                if (land_rent_contract == "是")
                                    m.land_rent_contract = 1;
                                else
                                    m.land_rent_contract = 0;

                                //是否抵押担保
                                var land_Is_mortgage = row.GetCell(25) == null ? "" : row.GetCell(25).ToString();
                                m.land_Is_mortgage = land_Is_mortgage;

                                //抵押担保对象
                                m.land_mortgage_obj = row.GetCell(26) == null ? "" : row.GetCell(26).ToString();
                                //抵押担保时间
                                var land_mortgage_date = row.GetCell(27) == null ? "" : row.GetCell(27).ToString();
                                if (!string.IsNullOrEmpty(land_mortgage_date))
                                    m.land_mortgage_date = Convert.ToDateTime(land_mortgage_date, dtFormat);
                                //抵押担保面积
                                if (row.GetCell(28) != null && !string.IsNullOrEmpty(row.GetCell(28).ToString()))
                                    m.land_mortgage_area = int.Parse(row.GetCell(28) == null ? "0" : row.GetCell(28).ToString());

                                //是否列入污染扰民搬迁
                                var land_is_pollution_removal = row.GetCell(29) == null ? "" : row.GetCell(29).ToString();
                                if (land_is_pollution_removal == "是")
                                    m.land_is_pollution_removal = 1;
                                else
                                    m.land_is_pollution_removal = 0;
                                //是否列入拆迁范围
                                var land_is_removal = row.GetCell(30) == null ? "" : row.GetCell(30).ToString();
                                if (land_is_removal == "是")
                                    m.land_is_removal = 1;
                                else
                                    m.land_is_removal = 0;
                                //是否列入棚户区改造计划
                                var land_is_shanty = row.GetCell(31) == null ? "" : row.GetCell(31).ToString();
                                if (land_is_shanty == "是")
                                    m.land_is_shanty = 1;
                                else
                                    m.land_is_shanty = 0;

                                //是否已列入开发土地
                                var land_Is_development = row.GetCell(32) == null ? "" : row.GetCell(32).ToString();
                                if (land_Is_development == "是")
                                    m.land_Is_development = 1;
                                else
                                    m.land_Is_development = 0;
                                //备注
                                m.land_remark = row.GetCell(33) == null ? "" : row.GetCell(33).ToString();

                                //默认导入的信息为未处置
                                m.land_isHandle = 0;

                                var t = landService.Add(m);
                                if (t)
                                    successCount += 1;
                                else
                                    failedCount += 1;
                                #endregion
                            }

                            resultMessage = string.Format("导入成功{0}条，导入失败{1}条", successCount, failedCount);

                        }
                        if (System.IO.File.Exists(strFilePath))
                        {
                            System.IO.File.Delete(strFilePath);
                        }
                    }
                }
                #region Original
                //        string fileLocation = Server.MapPath("~/Content/") + Path.GetFileName(landfile.FileName);
                //        if (System.IO.File.Exists(fileLocation))
                //        {
                //            System.IO.File.Delete(fileLocation);
                //        }
                //        Request.Files["landfile"].SaveAs(fileLocation);

                //        string excelConnectionString = string.Empty;
                //        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                //        if (fileExtension == ".xls")
                //        {
                //            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                //        }
                //        else if (fileExtension == ".xlsx")
                //        {

                //            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                //        }
                //        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                //        excelConnection.Open();
                //        DataTable dt = new DataTable();

                //        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //        if (dt == null)
                //        {
                //            return null;
                //        }

                //        String[] excelSheets = new String[dt.Rows.Count];
                //        int t = 0;
                //        foreach (DataRow row in dt.Rows)
                //        {
                //            excelSheets[t] = row["TABLE_NAME"].ToString();
                //            t++;
                //        }
                //        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);


                //        string query = string.Format("Select * from [{0}]", excelSheets[0]);
                //        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                //        {
                //            dataAdapter.Fill(ds);
                //        }
                //    }

                //    int successCount = 0;
                //    int failedCount = 0;
                //    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                //    {
                //        LandInfo m = new LandInfo();
                //        m.ZDBH = Decimal.Parse(ds.Tables[0].Rows[i][0].ToString());
                //        m.DJH = int.Parse(ds.Tables[0].Rows[i][1].ToString());
                //        m.land_area = int.Parse(ds.Tables[0].Rows[i][2].ToString());
                //        m.land_type = ds.Tables[0].Rows[i][3].ToString();
                //        m.land_location = ds.Tables[0].Rows[i][4].ToString();
                //        m.land_actual_user = ds.Tables[0].Rows[i][5].ToString();
                //        m.land_agency = 1;
                //        m.land_Is_IPO = 0;//(ds.Tables[0].Rows[i][7].ToString() == "是" ? true : false),
                //        //    //land_ownDate = "",
                //        m.land_price = Decimal.Parse(ds.Tables[0].Rows[i][9].ToString());
                //        m.land_range = ds.Tables[0].Rows[i][10].ToString();
                //        m.land_getway = ds.Tables[0].Rows[i][11].ToString();
                //        m.land_actual_user = ds.Tables[0].Rows[i][12].ToString();


                //        m.land_ownDate = DateTime.Now;
                //        m.land_isHandle = 0;
                //        m.land_Is_rent = "未出租";
                //        m.land_Is_mortgage = "未抵押/担保";

                //        var t = landService.Add(m);
                //        if (t)
                //            successCount += 1;
                //        else
                //            failedCount += 1;
                //    }
                //    resultMessage = string.Format("导入成功{0}条，导入失败{1}条", successCount, failedCount);
                //}

                #endregion
                ViewBag.ErrorMessage = resultMessage;
            }
            catch (Exception ex)
            {
                ViewBag.ErrorMessage = "导入失败:" + ex.Message;
                return View();
            }

            return View();
        }


        public ActionResult Search()
        {
            LandGetwayService wayService = new LandGetwayService();
            AgencyService agencyService = new AgencyService();

            var wayList = wayService.GetList();
            var agencyList = agencyService.GetList();

            ViewBag.GetWay = wayList;
            ViewBag.Agency = agencyList;

            ViewBag.Menu = "数据查询";
            return View();
        }

        [HttpPost]
        public ActionResult Query(string key, string value)
        {
            var sql = String.Format("{0} = '{1}'", key, value);

            var list = landService.QueryLandWithCondition(sql);

            return Content(JsonConvert.SerializeObject(list));
            //return Json(list, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public FileResult Export(string key, string value)
        {
            var sql = String.Format("{0} = '{1}'", key, value);
            var list = landService.QueryLandWithCondition(sql);

            var fileName = Server.MapPath("~/Template.xls");

            if (list == null || list.Count <= 0)
            {
                return File(fileName, "application/ms-excel", "fileName.xls");
            }

            HSSFWorkbook exportWorkBook;
            //读入刚复制的要导出的excel文件
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                exportWorkBook = new HSSFWorkbook(file);
                file.Close();
            }

            ISheet worksheet = exportWorkBook.GetSheetAt(0);

            int rowIndex = 1;
            foreach (LandInfo item in list)
            {
                var row = worksheet.CreateRow(rowIndex++);

                var cells = new List<ICell>();

                cells.Add(this.CreateCell(worksheet, row, 0, item.ZDBH, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 1, item.DJH, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 2, item.land_area, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 3, item.land_type, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 4, item.land_location, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 5, item.land_user, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 6, item.Agency.agency_name, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 7, item.land_Is_IPO == 0 ? "否" : "是", CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 8, item.land_ownDate, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 9, item.land_price, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 10, item.land_range, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 11, item.land_getway, CellType.String, null));
                cells.Add(this.CreateCell(worksheet, row, 12, item.land_actual_user, CellType.String, null));

                row.Cells.AddRange(cells);
            }

            MemoryStream ms = new MemoryStream();
            exportWorkBook.Write(ms);

            var exportFileName = DateTime.Now.ToString("yyyyMMdd") + ".xls";

            return File(ms.ToArray(), "application/ms-excel", exportFileName);
        }

        private ICell CreateCell(ISheet sheet, IRow row, int index, object value, CellType type, ICellStyle style)
        {
            var cell = row.CreateCell(index, type);

            cell.SetCellValue(value == null ? string.Empty : value.ToString());

            if (style != null)
                cell.CellStyle = style;

            return cell;
        }

        public ActionResult Statistic()
        {
            ViewBag.Menu = "数据统计";
            return View();
        }

        [HttpPost]
        public ActionResult QueryStatistic(string Key)
        {
            string sql = @"SELECT {0} as name , sum(land_area) as value FROM [dbo].[LandInfo] GROUP BY {0};";
            sql = String.Format(sql, Key);
            var list = landService.QueryLandWithSQL(sql);

            return Content(JsonConvert.SerializeObject(list));
            //return Json(list, JsonRequestBehavior.AllowGet);
        }

        public ActionResult Report(int id)
        {
            ViewBag.ID = id;

            return View();
        }

        [HttpPost]
        public ActionResult Report(string from, string to, int IsHandle)
        {
            ViewBag.ID = IsHandle;
            ViewBag.From = from;
            ViewBag.To = to;

            var condition = String.Format(" land_isHandle = {0} and land_ownDate > '{1}' and land_ownDate <= '{2}' ", IsHandle, from, to);

            string sql = @"SELECT count(*) as landcount, sum(land_area) as landarea, sum(land_price) as landprice FROM [dbo].[LandInfo] WHERE " + condition;

            string getwayListSQL = @"SELECT b.way_name as TempKey, ISNULL(SUM (land_area),0) as TempValue from (select * from LandInfo where " +
                condition + ") a RIGHT JOIN LandGetway b ON a.land_getway = b.way_name group by b.way_name;";

            string typeSQL = @"SELECT b.type_name as TempKey, ISNULL(SUM (land_area),0) as TempValue from (select * from LandInfo where " +
                condition + ") a RIGHT JOIN landtype b on a.land_type = b.type_name  GROUP BY b.type_name;";

            string rangeSQL = @"SELECT b.range_name as TempKey, ISNULL(SUM (land_area),0) as TempValue from (select * from LandInfo where " +
                condition + ") a RIGHT JOIN LandRange b ON a.land_range = b.range_name group by b.range_name;";

            var report = landService.GetReportModelWithSQL(sql);

            var getwayList = landService.GetDicWithSQL(getwayListSQL);
            var typeList = landService.GetDicWithSQL(typeSQL);
            var rangeList = landService.GetDicWithSQL(rangeSQL);

            ViewBag.GetWay = getwayList;
            ViewBag.Type = typeList;
            ViewBag.Range = rangeList;

            return View(report);
        }

        public ActionResult Create()
        {
            AgencyService agencyService = new AgencyService();
            LandRangeService rangeService = new LandRangeService();
            LandGetwayService wayService = new LandGetwayService();
            LandTypeService typeService = new LandTypeService();
            CertificateService certifiService = new CertificateService();
            ReasonService reasonService = new ReasonService();

            var certiList = certifiService.GetList();
            var reasonList = reasonService.GetList();
            var typeList = typeService.GetList();
            var agencyList = agencyService.GetList();
            var landRangeList = rangeService.GetList();
            var wayList = wayService.GetList();

            ViewBag.LandRange = landRangeList;
            ViewBag.Agency = agencyList;
            ViewBag.GetWay = wayList;
            ViewBag.Reason = reasonList;
            ViewBag.Certification = certiList;
            ViewBag.LandType = typeList;


            return View();
        }

        //
        // POST: /Land/Create

        [HttpPost]
        public ActionResult Create(LandInfo model)
        {
            try
            {
                var result = landService.Add(model);

                if (result)
                {
                    return RedirectToAction("Index");
                }
                else
                {
                    ViewBag.ErrorMessage = "录入失败";
                    return View();
                }
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Land/Edit/5

        public ActionResult Edit(decimal id)
        {
            var model = landService.FindByID(id);

            AgencyService agencyService = new AgencyService();
            LandRangeService rangeService = new LandRangeService();
            LandGetwayService wayService = new LandGetwayService();
            LandTypeService typeService = new LandTypeService();
            CertificateService certifiService = new CertificateService();
            ReasonService reasonService = new ReasonService();

            var certiList = certifiService.GetList();
            var reasonList = reasonService.GetList();
            var typeList = typeService.GetList();
            var agencyList = agencyService.GetList();
            var landRangeList = rangeService.GetList();
            var wayList = wayService.GetList();

            ViewBag.LandRange = landRangeList;
            ViewBag.Agency = agencyList;
            ViewBag.GetWay = wayList;
            ViewBag.Reason = reasonList;
            ViewBag.Certification = certiList;
            ViewBag.LandType = typeList;

            ViewBag.Menu = "数据修改";
            return View(model);
        }

        //
        // POST: /Land/Edit/5

        [HttpPost]
        public ActionResult Edit(LandInfo model)
        {
            try
            {
                var result = landService.Edit(model);

                return RedirectToAction("Index");
            }
            catch
            {
                return View(model);
            }
        }

        public ActionResult Handle(decimal id)
        {
            var model = landService.FindByID(id);

            return View(model);
        }

        [HttpPost]
        public ActionResult Handle(LandHandle model)
        {
            var landInfo = landService.FindByID(Decimal.Parse(model.ZDBH));

            landInfo.land_isHandle = 1;

            landService.Edit(landInfo);

            HandleService handleService = new HandleService();
            try
            {
                var result = handleService.Add(model);

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

    }
}
